/*
 * SQL manager for database access.
 *
 * License: LGPL v3
 */
package org.passwordstore.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author thotheolh
 */
public class SQLManager {

    private String connPath = null;
    private String username = null;
    private String password = null;
    private Connection conn = null;
    private boolean isConnected = false;

    public SQLManager() {
    }

    /**
     * Connect database
     * @param connPath
     * @param username
     * @param password
     */
    public void connectDB(String connPath, String username, String password) {
        this.setConnPath(connPath);
        this.setUsername(username);
        this.setPassword(password);
        setConn(new DBManager(this.getConnPath(), this.getUsername(), this.getPassword()).getConnection());
        if (getConn() != null) {
            isConnected = true;
            new AutoSetup(conn).init();
        }
    }

    /**
     * Disconnect database
     */
    public void disconnectDB() {
        try {
            getConn().close();
            setConn(null);
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Commit database
     */
    public void commit() {
        try {
            getConn().commit();
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * SQL Statements Here
     */
    //Add password info
    public boolean addPassword(Password password) {
        boolean ok = false;
        try {
            PreparedStatement pstmt = getConn().prepareStatement("INSERT INTO PASSWORDSTORE VALUES (?,?,?)");
            pstmt.setString(1, password.getUname());
            pstmt.setString(2, password.getUpass());
            pstmt.setString(3, password.getRplace());
            int num = pstmt.executeUpdate();
            if (num == 1) {
                ok = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ok;
    }

    //Update password info
    public boolean updatePassword(Password password, Password oldpass) {
        boolean ok = false;
        try {
            PreparedStatement pstmt = getConn().prepareStatement("UPDATE PASSWORDSTORE SET USERNAME = ? , PASSWORD = ? , REQUIRED_PLACE = ? where USERNAME = ? AND PASSWORD = ? AND REQUIRED_PLACE = ?");
            pstmt.setString(1, password.getUname());
            pstmt.setString(2, password.getUpass());
            pstmt.setString(3, password.getRplace());
            pstmt.setString(4, oldpass.getUname());
            pstmt.setString(5, oldpass.getUpass());
            pstmt.setString(6, oldpass.getRplace());
            //System.out.println(password.toString());
            int num = pstmt.executeUpdate();
            if (num == 1) {
                ok = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ok;
    }

    //Delete password info
    public boolean deletePassword(Password password) {
        boolean ok = false;
        try {
            PreparedStatement pstmt = getConn().prepareStatement("DELETE FROM PASSWORDSTORE WHERE USERNAME=? AND REQUIRED_PLACE=?");
            pstmt.setString(1, password.getUname());
            pstmt.setString(2, password.getRplace());
            int num = pstmt.executeUpdate();
            if (num == 1) {
                ok = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ok;
    }

    //View all password info but without passwords for safety reasons
    public ArrayList viewAllPasswords() {
        ArrayList pwdList = new ArrayList();
        Password password;
        try {
            PreparedStatement pstmt = getConn().prepareStatement("SELECT * FROM PASSWORDSTORE");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                password = new Password();
                password.setUname(rs.getString("USERNAME"));
                password.setUpass(null);
                password.setRplace(rs.getString("REQUIRED_PLACE"));
                pwdList.add(password);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pwdList;
    }

    //View all password info including passwords
    public ArrayList viewAllPasswordsWPass() {
        ArrayList pwdList = new ArrayList();
        Password password;
        try {
            PreparedStatement pstmt = getConn().prepareStatement("SELECT * FROM PASSWORDSTORE");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                password = new Password();
                password.setUname(rs.getString("USERNAME"));
                password.setUpass(rs.getString("PASSWORD"));
                password.setRplace(rs.getString("REQUIRED_PLACE"));
                pwdList.add(password);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pwdList;
    }

    //View a particular password info
    public String viewPassword(Password password) {
        try {
            PreparedStatement pstmt = getConn().prepareStatement("SELECT PASSWORD FROM PASSWORDSTORE WHERE USERNAME = ? AND REQUIRED_PLACE = ?");
            pstmt.setString(1, password.getUname());
            pstmt.setString(2, password.getRplace());
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString("PASSWORD");
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList searchPasswords(String parameters) {
        ArrayList pwdList = new ArrayList();
        Password password;
        int count = 0;
        try {
            PreparedStatement pstmt = getConn().prepareStatement("SELECT * FROM PASSWORDSTORE WHERE LOWER(USERNAME) LIKE LOWER(?) OR LOWER(REQUIRED_PLACE) LIKE LOWER(?)");
            pstmt.setString(1, "%" + parameters + "%");
            pstmt.setString(2, "%" + parameters + "%");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                password = new Password();
                password.setUname(rs.getString("USERNAME"));
                password.setUpass(null);
                password.setRplace(rs.getString("REQUIRED_PLACE"));
                pwdList.add(password);
                count++;
                //System.out.println("Found " + count);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pwdList;
    }

    //Check if passord info already exists
    public boolean isPasswordExist(Password password) {
        try {
            PreparedStatement pstmt = getConn().prepareStatement("SELECT * FROM PASSWORDSTORE WHERE USERNAME = ? AND REQUIRED_PLACE = ?");
            pstmt.setString(1, password.getUname());
            pstmt.setString(2, password.getRplace());
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    //Execute plain sql query
    public ResultSet executePlainQuery(String query) {
        try {
            PreparedStatement pstmt = getConn().prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    //Execute plain sql update
    public int executePlainUpdate(String query) {
        try {
            PreparedStatement pstmt = getConn().prepareStatement(query);
            int numRows = pstmt.executeUpdate();
            return numRows;
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
            return 0;
        }
    }

    public byte[] encrypt(byte[] keyBytes, byte[] data) {
        try {
            PreparedStatement pstmt = getConn().prepareStatement("CALL ENCRYPT('AES', ?, ?)");
            pstmt.setBytes(1, keyBytes);
            pstmt.setBytes(2, data);
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            return rs.getBytes(1);
            //return numRows;
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
            //return 0;
            return null;
        }
    }

    public byte[] decrypt(byte[] keyBytes, byte[] data) {
        try {
            PreparedStatement pstmt = getConn().prepareStatement("CALL DECRYPT('AES', ?, ?)");
            pstmt.setBytes(1, keyBytes);
            pstmt.setBytes(2, data);
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            return rs.getBytes(1);
            //return numRows;
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
            return null;
            //return 0;
        }
    }

    public byte[] hashPwd(String password) {
        try {
            PreparedStatement pstmt = getConn().prepareStatement("CALL HASH('SHA256', ?, 1000)");
            pstmt.setBytes(1, password.getBytes());
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            return rs.getBytes(1);
            //return numRows;
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
            return null;
            //return 0;
        }
    }

    public boolean changeDBPassword(String password) {
        try {
            PreparedStatement pstmt = getConn().prepareStatement("SET PASSWORD ?");
            pstmt.setString(1, password);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    /**
     * @return the connPath
     */
    public String getConnPath() {
        return connPath;
    }

    /**
     * @param connPath the connPath to set
     */
    public void setConnPath(String connPath) {
        this.connPath = connPath;
    }

    /**
     * @return the username
     */
    public String getUsername() {
        return username;
    }

    /**
     * @param username the username to set
     */
    public void setUsername(String username) {
        this.username = username;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    public void setPassword(String password) {
        this.password = password;
    }

    /**
     * Check if database connection exists
     * @return boolean
     */
    public boolean isConnected() {
        if (getConn() != null) {
            isConnected = true;
        } else {
            isConnected = false;
        }
        return isConnected;
    }

    /**
     * @param isConnected the isConnected to set
     */
    public void setIsConnected(boolean isConnected) {
        this.isConnected = isConnected;
    }

    /**
     * @return the conn
     */
    public Connection getConn() {
        return conn;
    }

    /**
     * @param conn the conn to set
     */
    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public boolean terminateAcct() {
        try {
            PreparedStatement pstmt = getConn().prepareStatement("DROP ALL OBJECTS DELETE FILES");
            int num = pstmt.executeUpdate();
            if (num >= 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean wipePasswordTable() {
        try {
            PreparedStatement pstmt = getConn().prepareStatement("DELETE FROM PASSWORDSTORE");
            int num = pstmt.executeUpdate();
            if (num >= 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public ArrayList searchPasswordsWPass(String parameters) {
        ArrayList pwdList = new ArrayList();
        Password password;
        int count = 0;
        try {
            PreparedStatement pstmt = getConn().prepareStatement("SELECT * FROM PASSWORDSTORE WHERE LOWER(USERNAME) LIKE LOWER(?) OR LOWER(REQUIRED_PLACE) LIKE LOWER(?)");
            pstmt.setString(1, "%" + parameters + "%");
            pstmt.setString(2, "%" + parameters + "%");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                password = new Password();
                password.setUname(rs.getString("USERNAME"));
                password.setUpass(rs.getString("PASSWORD"));
                password.setRplace(rs.getString("REQUIRED_PLACE"));
                pwdList.add(password);
                count++;
                //System.out.println("Found " + count);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pwdList;
    }

    public ArrayList getAllPasswords() {
        ArrayList pwdList = new ArrayList();
        Password password;
        int count = 0;
        try {
            PreparedStatement pstmt = getConn().prepareStatement("SELECT * FROM PASSWORDSTORE");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                password = new Password();
                password.setUname(rs.getString("USERNAME"));
                password.setUpass(rs.getString("PASSWORD"));
                password.setRplace(rs.getString("REQUIRED_PLACE"));
                pwdList.add(password);
                count++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pwdList;
    }
}
